﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Pr0ject0.Models
{
    public class EducationDB
    {
        private string connection;
        private List<EducationModel> education;
        public List<EducationModel> EducationList { get { return new List<EducationModel>(education); } }

        public EducationDB()
        {
            connection = "DB";
            update();
        }

        public EducationDB(string connectionString)
        {
            connection = connectionString;
            update();
        }

        public void update()
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[connection].ToString());
            SqlCommand cmd = new SqlCommand("SELECT * FROM [Education]", conn);
            conn.Open();
            SqlDataReader reader = cmd.ExecuteReader();

            education = new List<EducationModel>();

            while (reader.Read())
            {
                EducationModel model = new EducationModel();
                model.ID = Convert.ToInt32(reader["Id"].ToString());
                model.name = reader["name"].ToString();
                model.major = reader["major"].ToString();
                model.startYear = Convert.ToInt32(reader["startYear"].ToString());
                model.endYear = Convert.ToInt32(reader["endYear"].ToString());
                model.isGraduate = Convert.ToBoolean(reader["isGraduate"].ToString());
                education.Add(model);
            }

            reader.Close();
            conn.Close();
        }

        public void Add(List<EducationModel> list)
        {
            foreach (EducationModel e in list)
            {
                Add(e);
            }
        }

        public void Add(EducationModel model)
        {
            if (model.name != null && model.name != "")
            {
                string insert = string.Format("INSERT INTO Education VALUES ({0}, '{1}', '{2}', '{3}', '{4}', {5})",
                    model.ID, model.name, model.major, model.startYear, model.endYear, Convert.ToInt32(model.isGraduate));
                SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[connection].ToString());
                SqlCommand cmd = new SqlCommand(insert, conn);

                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();

                update();
            }
        }

    }
}